﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace GPExplorer{
    internal static class Database{
        internal const string DB_NAME = "Tab.db3";
        internal static string DataSource = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\GPExplorer\" + DB_NAME;

        internal static void CreateTabTableDatabase(){
            try{
                Directory.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) +
                                          @"\GPExplorer\");
                SQLiteConnection.CreateFile(DataSource);
            }
            catch (UnauthorizedAccessException _e){
                MessageBox.Show(_e.Message);
            }
        }

        internal static void CreateTabsTable(){
            const string CREATE_QUERY = @"CREATE TABLE 'TabFiles' (
	                                    'Path'	varchar(100) NOT NULL,
	                                    'Artist'	varchar(100) NOT NULL,
	                                    'Song'	varchar(100) NOT NULL,
	                                    'Version'	VarChar(10) NOT NULL,
	                                    'Date'	VARCHAR(50),
	                                    'Library'	varchar(100) NOT NULL,
	                                    PRIMARY KEY(Path,Library)
                                        );";
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)){
                using (SQLiteCommand _command = new SQLiteCommand(_con)){
                    _con.Open();
                    _command.CommandText = CREATE_QUERY;
                    _command.ExecuteNonQuery();
                }
            }
        }

        internal static void InsertTab(GpFile file, string library){
            try{
                using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource + ";UseUTF16Encoding=true")) {
                    using (SQLiteCommand _command = new SQLiteCommand(_con)) {
                        _con.Open();
                        _command.CommandText = @"Insert into TabFiles (Path,Artist,Song,Version,Date,Library)" +
                                               " Values ('" + file.FilePath + "','" +
                                               file.Artist + "','" +
                                               file.SongName + "','" +
                                               file.Version + "','" +
                                               file.FileDate + "','" +
                                               library + "');";

                        _command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception _e){
                MessageBox.Show(@"Error entering Tab into database " + _e.Message, @"Insert Error", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
        }

        internal static void InsertTab(PtbFile file, string library) {
            try {
                using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource + ";UseUTF16Encoding=true")) {
                    using (SQLiteCommand _command = new SQLiteCommand(_con)) {
                        _con.Open();
                        _command.CommandText = @"Insert into TabFiles (Path,Artist,Song,Version,Date,Library)" +
                                               " Values ('" + file.FilePath + "','" +
                                               file.Artist + "','" +
                                               file.SongName + "','" +
                                               file.Version + "','" +
                                               file.FileDate + "','" +
                                               library + "');";

                        _command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception _e) {
                MessageBox.Show(@"Error entering Tab into database " + _e.Message, @"Insert Error", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
        }

        internal static void DeleteTabFromLibrary(GpFile file, string library){
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)){
                using (SQLiteCommand _command = new SQLiteCommand(_con)){
                    _con.Open();
                    _command.CommandText = "Delete from TabFiles Where Path = '" + file.FilePath + "' and  Library ='" +
                                           library + "'";

                    _command.ExecuteNonQuery();
                }
            }
        }

        internal static List<GpFile> GetGpTabsFromLibrary(string library){
            List<GpFile> _gpFiles = new List<GpFile>();
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)){
                using (SQLiteCommand _command = new SQLiteCommand(_con)){
                    _con.Open();
                    _command.CommandText = "Select * from TabFiles Where Library = '" + library + "' and Version Like '%gp%' order by Artist asc";
                    using (SQLiteDataReader _reader = _command.ExecuteReader()){
                        while (_reader.Read()){
                            GpFile _file = new GpFile();
                            _file.FilePath = _reader["Path"].ToString();
                            _file.Artist = _reader["Artist"].ToString();
                            _file.SongName = _reader["Song"].ToString();
                            _file.Version = _reader["Version"].ToString();
                            _file.FileDate = _reader["Date"].ToString();
                            _gpFiles.Add(_file);
                        }
                    }
                }
                return _gpFiles;
            }
        }

        internal static List<PtbFile> GetPtbTabsFromLibrary(string library) {
            List<PtbFile> _ptbFiles = new List<PtbFile>();
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)) {
                using (SQLiteCommand _command = new SQLiteCommand(_con)) {
                    _con.Open();
                    _command.CommandText = "Select * from TabFiles Where Library = '" + library + "' and Version Like '%ptb' order by Artist asc";
                    using (SQLiteDataReader _reader = _command.ExecuteReader()) {
                        while (_reader.Read()) {
                            PtbFile _file = new PtbFile();
                            _file.FilePath = _reader["Path"].ToString();
                            _file.Artist = _reader["Artist"].ToString();
                            _file.SongName = _reader["Song"].ToString();
                            _file.Version = _reader["Version"].ToString();
                            _file.FileDate = _reader["Date"].ToString();
                            _ptbFiles.Add(_file);
                        }
                    }
                }
                return _ptbFiles;
            }
        }

        internal static ICollection<GpFile> GetSongsByArtist(string artist, string library){
            List<GpFile> _gpFiles = new List<GpFile>();
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)){
                using (SQLiteCommand _command = new SQLiteCommand(_con)){
                    _con.Open();
                    _command.CommandText = "Select * from TabFiles Where Library = '" + library + "' and Artist ='" +
                                           artist + "'";
                    using (SQLiteDataReader _reader = _command.ExecuteReader()){
                        while (_reader.Read()){
                            GpFile _file = new GpFile();
                            _file.FilePath = _reader["Path"].ToString();
                            _file.Artist = _reader["Artist"].ToString();
                            _file.SongName = _reader["Song"].ToString();
                            _file.Version = _reader["Version"].ToString();
                            _file.FileDate = _reader["Date"].ToString();
                            _gpFiles.Add(_file);
                        }
                    }
                }
                return _gpFiles;
            }
        }

        internal static ICollection<string> GetLibrariesDistinct(){
            ICollection<string> _libs = new List<string>();
            const string LIBS_QUERY = "Select distinct Library from TabFiles;";

            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)){
                using (SQLiteCommand _command = new SQLiteCommand(_con)){
                    _con.Open();
                    _command.CommandText = LIBS_QUERY;
                    using (SQLiteDataReader _reader = _command.ExecuteReader()){
                        while (_reader.Read()){
                            _libs.Add(_reader[0].ToString());
                        }
                    }
                }
            }
            return _libs;
        }

        internal static void DeleteLibrary(string library) {
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)) {
                using (SQLiteCommand _command = new SQLiteCommand(_con)) {
                    _con.Open();
                    _command.CommandText = "Delete from TabFiles Where  Library ='" + library + "'";

                    _command.ExecuteNonQuery();
                }
            }
        }

        internal static List<string> GetLibrariesArtistsDistinct(string libname){
            List<string> _artists = new List<string>();
            string _artistQuery = "Select distinct Artist from TabFiles where Library='" + libname +"' order by Artist asc";
            using (SQLiteConnection _con = new SQLiteConnection("data source=" + DataSource)) {
                using (SQLiteCommand _command = new SQLiteCommand(_con)) {
                    _con.Open();
                    _command.CommandText = _artistQuery;
                    using (SQLiteDataReader _reader = _command.ExecuteReader()) {
                        while (_reader.Read()) {
                            _artists.Add(_reader[0].ToString());
                        }
                    }
                }
            }
            return _artists;
        }
    }
}